package layuidemo.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import layuidemo.dao.StudentDao;
import layuidemo.entity.Student;
import layuidemo.util.JdbcUtil;

public class StudentDaoImpl implements StudentDao {

	@Override
	public List<Student> findAll(int page,int size,String sname,int ageMin,int ageMax) {
		List<Student> list = new ArrayList<Student>();
		Connection conn = JdbcUtil.getConnection();
		int n = (page-1)*size;
		String sql = "select * from student where 1=1";
		if(sname!=null&&sname!=""){
			sql += " and sname='"+sname+"'";
		}
		if(ageMin>0){
			sql += " and age>="+ageMin;
		}
		if(ageMax>0){
			sql += " and age<="+ageMax;
		}
		sql +=" limit "+n+","+size;
		System.out.println(sql);
		try {
			Statement st = conn.createStatement();
			ResultSet rs =st.executeQuery(sql);
			while(rs.next()){
				int sid = rs.getInt("sid");
				String sname2 = rs.getString("sname");
				int age = rs.getInt("age");
				Student s = new Student(sid, sname2, age);
				list.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int getCount(String sname,int ageMin,int ageMax) {
		int n = 0;
		Connection conn = JdbcUtil.getConnection();
		String sql = "select count(*) from student where 1=1";
		if(sname!=null&&sname!=""){
			sql += " and sname='"+sname+"'";
		}
		if(ageMin>0){
			sql += " and age>="+ageMin;
		}
		if(ageMax>0){
			sql += " and age<="+ageMax;
		}
		try {
			Statement st = conn.createStatement();
			ResultSet rs =st.executeQuery(sql);
			while(rs.next()){
				n = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return n;
	}

	@Override
	public int del(int id) {
		String sql = "delete from student where sid="+id;
		return JdbcUtil.update(sql);
	}

	@Override
	public int edit(Student s) {
		String sql = "update student set sname='"+s.getSname()+"',age="+s.getAge()+" where sid = "+s.getSid();
		return JdbcUtil.update(sql);
	}

	@Override
	public int add(Student s) {
		String sql = "insert into student values(null,'"+s.getSname()+"',"+s.getAge()+")";
		return JdbcUtil.update(sql);
	}

}
